package com.shopping.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.shopping.modean.Category;
import com.shopping.util.DBHelper;

public class CategoryDAO {

	/**
	 * 新增category 
	 */
	public void insert(Category c) {
		
		Connection conn = DBHelper.getConn();
		String sql = "insert into category(id,pid,name,descr,leaf,grade) values(null,?,?,?,?,?)";
		System.out.println(sql);
		PreparedStatement pstmt = DBHelper.getPStmt(conn, sql);
		try {
			pstmt.setInt(1, c.getPid());
			pstmt.setString(2, c.getName());
			pstmt.setString(3, c.getDescr());
			pstmt.setInt(4, c.isLeaf() ? 1:0);
			pstmt.setInt(5,c.getGrade());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeStmt(pstmt);
			DBHelper.closeConn(conn);
		}
		
	}

	/**
	 * 获取所有的Category 
	 */
	public List<Category> getCategorys() {
		List<Category> categorys = new ArrayList<Category>();
		Connection conn = null;
		ResultSet rs = null;
		try {
			conn = DBHelper.getConn();
			String sql = "select * from category";
			rs = DBHelper.executeQuery(conn, sql);
			while(rs.next()) {
				Category c = new Category();
				c.setId(rs.getInt("id"));
				c.setName(rs.getString("name"));
				c.setDescr(rs.getString("descr"));
				c.setPid(rs.getInt("pid"));
				c.setGrade(rs.getInt("grade"));
				c.setLeaf(rs.getInt("leaf") == 1 ? true : false);
				categorys.add(c);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeRs(rs);
			DBHelper.closeConn(conn);
		}
		return categorys;
	}
	
	
	/**
	 * 根据pid获取所有的Category 
	 */
	public List<Category> getCategorys(int pid) {
		List<Category> categorys = new ArrayList<Category>();
		Connection conn = null;
		ResultSet rs = null;
		try {
			conn = DBHelper.getConn();
			String sql = "select * from category where pid = " + pid;
			rs = DBHelper.executeQuery(conn, sql);
			while(rs.next()) {
				Category c = new Category();
				c.setId(rs.getInt("id"));
				c.setName(rs.getString("name"));
				c.setDescr(rs.getString("descr"));
				c.setPid(rs.getInt("pid"));
				c.setGrade(rs.getInt("grade"));
				c.setLeaf(rs.getInt("leaf") == 1 ? true : false);
				categorys.add(c);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeRs(rs);
			DBHelper.closeConn(conn);
		}
		return categorys;
	}
	
	/**
	 * 根据ID获取category
	 * 
	 * return ID不存在时返回null
	 * 
	 */
	public Category getCategoryById(int id) {
		Category c = null;
		Connection conn = null;
		ResultSet rs = null;
		try {
			conn = DBHelper.getConn();
			String sql = "select * from category where id = " + id;
			rs = DBHelper.executeQuery(conn, sql);
			if(rs.next()) {
				c = new Category();
				c.setId(rs.getInt("id"));
				c.setName(rs.getString("name"));
				c.setDescr(rs.getString("descr"));
				c.setPid(rs.getInt("pid"));
				c.setGrade(rs.getInt("grade"));
				c.setLeaf(rs.getInt("leaf") == 1 ? true : false); 
			} 
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeRs(rs);
			DBHelper.closeConn(conn);
		}
		return c;
	}
	
	/**
	 * 根据PID获取子节点
	 */
	public List<Category> getCategorysByPid(int pid) {
		List<Category> categorys = new ArrayList<Category>();
		Connection conn = null;
		ResultSet rs = null;
		try {
			conn = DBHelper.getConn();
			String sql = "select * from category where pid = " + pid;
			rs = DBHelper.executeQuery(conn, sql);
			while(rs.next()) {
				Category c = new Category();
				c.setId(rs.getInt("id"));
				c.setName(rs.getString("name"));
				c.setDescr(rs.getString("desrc"));
				c.setPid(rs.getInt("pid"));
				c.setGrade(rs.getInt("grade"));
				c.setLeaf(rs.getInt("leaf") == 1 ? true : false); 
				categorys.add(c);
				if(!c.isLeaf()) {
					getCategorysByPid(c.getId());
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeRs(rs);
			DBHelper.closeConn(conn);
		}
		return categorys;
	}

	/**
	 * 更新父节点的叶子属性
	 */
	public void updateCategoryLeafById(int pid, boolean isLeaf) {
		Connection conn = DBHelper.getConn();
		String sql = "update category set leaf = ? where id = ?";
		System.out.println(sql);
		PreparedStatement pstmt = DBHelper.getPStmt(conn, sql);
		try {
			pstmt.setInt(1, isLeaf ? 1:0);
			pstmt.setInt(2, pid);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeStmt(pstmt);
			DBHelper.closeConn(conn);
		}
	}
	
	/**
	 * 根据id更新
	 */
	public void updateById(Category c) {
		Connection conn = DBHelper.getConn();
		String sql = "update category set name = ?,descr = ? where id = ?";
		System.out.println(sql);
		PreparedStatement pstmt = DBHelper.getPStmt(conn, sql);
		try {
			pstmt.setString(1, c.getName());
			pstmt.setString(2, c.getDescr());
			pstmt.setInt(3, c.getId());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeStmt(pstmt);
			DBHelper.closeConn(conn);
		}
	}
	
	/**
	 * 根据id删除分类
	 */
	public void deleteById(int id) {
		Connection conn = DBHelper.getConn();
		String sql = "delete from category where id = " + id + " or pid = " + id;
		System.out.println(sql);
		PreparedStatement pstmt = DBHelper.getPStmt(conn, sql);
		try {
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeStmt(pstmt);
			DBHelper.closeConn(conn);
		}
	}
	
	
		
}
